import threading
from hashlib import md5
from lxml import etree
from selenium import webdriver
import time
from queue import Queue
from pymysql import connect
from urllib.parse import quote


class MusicSpider:

    def __init__(self):
        self.start_url = 'http://music.163.com/#/discover/playlist/'
        self.conn = connect(host='localhost', port=3306, user='root', password='root',
                            database='163music_spider', charset='utf8')
        self.cursor = self.conn.cursor()
        self.cate_quene = Queue()
        self.playlist_quene = Queue()
        self.driver = webdriver.Chrome()

    def get_cate(self):
        self.driver.get(self.start_url)
        self.driver.switch_to.frame('g_iframe')
        html_str = self.driver.page_source
        element = etree.HTML(html_str)
        dl_list = element.xpath('//dl[@class="f-cb"]')
        for dl in dl_list:
            for cate in dl.xpath('./dd/a/text()'):
                sql = 'select * from category where name = ("%s")' % cate
                self.cursor.execute(sql)
                db_cate = self.cursor.fetchone()
                if db_cate is None:
                    sql = 'insert into category (name) values ("%s")' % cate
                    self.cursor.execute(sql)
                    self.conn.commit()
                    self.cate_quene.put({
                        'cate_id': self.cursor.lastrowid,
                        'cate_url': 'http://music.163.com/#/discover/playlist/?cat=' + quote(cate)
                    })
                else:
                    self.cate_quene.put({
                        'cate_id': int(db_cate[0]),
                        'cate_url': 'http://music.163.com/#/discover/playlist/?cat=' + quote(cate)
                    })
        # 关闭窗口
        self.driver.quit()

    # 保存歌单
    def save_playlist(self):
        while True:
            cate = self.cate_quene.get()
            cate_id = cate['cate_id']
            cate_url = cate['cate_url']
            thread_diver = webdriver.Chrome()
            thread_diver.get(cate_url)
            thread_diver.switch_to.frame('g_iframe')
            html_str = thread_diver.page_source
            element = etree.HTML(html_str)
            li_list = element.xpath('//ul[@id="m-pl-container"]/li')
            for li in li_list:
                playlist_img = li.xpath('.//img[@class="j-flag"]/@src')[0]
                playlist_url = 'http://music.163.com' + li.xpath('./div[1]/a/@href')[0]
                playlist_user = li.xpath('.//a[@class="nm nm-icn f-thide s-fc3"]/text()')[0]
                playlist_name = li.xpath('./div[1]/a/@title')[0] + '_' + playlist_user
                # 判断歌单是否已存在
                sql = 'select * from playlist where name = %s'
                self.cursor.execute(sql, [playlist_name])
                db_playlist = self.cursor.fetchone()
                if db_playlist is None:
                    # 歌单不存在,添加歌单
                    sql = "insert into playlist (name,user,url,img) values (%s,%s,%s,%s)"
                    self.cursor.execute(sql, [playlist_name, playlist_user, playlist_url, playlist_img])
                    self.conn.commit()
                    playlist_id = self.cursor.lastrowid
                else:
                    playlist_id = int(db_playlist[0])
                # 维护一张分类对应歌单的表cate_playlist
                sql = 'select * from cate_playlist where cate_id=%d and playlist_id=%d' % (cate_id, playlist_id)
                self.cursor.execute(sql)
                if self.cursor.fetchone() is None:
                    sql = 'insert into cate_playlist (cate_id, playlist_id) values (%d,%d)' % (cate_id, playlist_id)
                    self.cursor.execute(sql)
                    self.conn.commit()
                # 推进队列，等待下载歌单里的歌曲信息
                self.playlist_quene.put({
                        'playlist_id': playlist_id,
                        'playlist_url': playlist_url
                })

            # 检测下一页
            next_url = element.xpath('//a[text()="下一页"]/@href')

            if len(next_url) > 0 and next_url[0] != 'javascript:void(0)':
                self.cate_quene.put(
                    {
                        'cate_id': cate_id,
                        'cate_url': 'http://music.163.com' + next_url[0]
                    }
                )
            # 关闭当前窗口
            thread_diver.close()
            self.cate_quene.task_done()

    def save_song(self):
        while True:
            playlist = {'playlist_url':'http://music.163.com/#/playlist?id=2190717778', 'playlist_id': 1}
            # playlist = self.playlist_quene.get()
            thread_driver = webdriver.Chrome()
            #thread_driver.get(playlist['playlist_url'])
            thread_driver.get('http://music.163.com/#/playlist?id=2190717778')
            thread_driver.switch_to.frame('g_iframe')
            html_str = thread_driver.page_source
            element = etree.HTML(html_str)
            tr_list = element.xpath('//div[@id="song-list-pre-cache"]//tbody/tr')
            for tr in tr_list:
                name = tr.xpath('./td[2]//b/@title')[0]
                time_str = tr.xpath('./td[3]//text()')[0]
                singer = tr.xpath('./td[4]//span/@title')[0]
                album = tr.xpath('./td[5]//a/@title')[0]
                url = 'http://music.163.com' + tr.xpath('./td[2]//a/@href')[0]
                hash_key = '_'.join([name, time_str, singer, album])
                hash_str = md5(hash_key.encode()).hexdigest()
                # 判断歌曲是否已存在
                sql = 'select * from song where hash=%s'
                self.cursor.execute(sql, [hash_str])
                db_song = self.cursor.fetchone()
                if db_song is None:
                    # 添加新歌曲
                    sql = f"insert into song (name,time,singer,album,url,hash) values ('{name}', '{time_str}', '{singer}', '{album}','{url}', '{hash_str}')"
                    self.cursor.execute(sql)
                    self.conn.commit()
                    song_id = self.cursor.lastrowid
                else:
                    song_id = int(db_song[0])
                # 维护一张歌单和歌曲的关系表
                sql = 'select * from playlist_song where playlist_id=%d and song_id=%d' % (playlist['playlist_id'], song_id)
                self.cursor.execute(sql)
                if self.cursor.fetchone() is None:
                    sql = 'insert into playlist_song (playlist_id, song_id) values (%d,%d)' % (playlist['playlist_id'], song_id)
                    self.cursor.execute(sql)
                    self.conn.commit()
            #self.playlist_quene.task_done()
            # 关闭窗口
            thread_driver.close()

    def run(self):
        thread_list = []

        t_cate = threading.Thread(target=self.get_cate)
        thread_list.append(t_cate)

        for i in range(3):
            t_playlist = threading.Thread(target=self.save_playlist)
            thread_list.append(t_playlist)

        for i in range(4):
            t_song = threading.Thread(target=self.save_song)
            thread_list.append(t_song)

        for t in thread_list:
            t.setDaemon(True)
            t.run()

        for q in [self.cate_quene, self.playlist_quene]:
            q.join()

        print('over')


if __name__ == '__main__':
    music = MusicSpider()
    music.save_song()
